package cn.aresoft.excel.utils;

import cn.aresoft.configuration.ExcelSpringContext;
import cn.aresoft.excel.entity.ExcelDictModel;
import cn.aresoft.excel.entity.ExcelFieldEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


/**
 * @author yangfeng
 * @description:
 * @date 2024/1/31 17:44
 */
public class ExcelDictSheetUtil {

    /**
     * 静态变量
     */
    public static final String[] CELL_NAME = {"A", "B", "C", "D", "E", "F", "G", "H", "I",
            "J", "K", "L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};

    /**
     * 字典map: key-字典类型  value-获取字典公式
     */
    private Map<String,String> dictMap = new HashMap<>();

    private int dictColIndex = 0;

    private Workbook book;

    private Sheet dictSheet;

    private static final ExcelStyleUtil excelStyleUtil = ExcelSpringContext.getBean(ExcelStyleUtil.class);


    public ExcelDictSheetUtil() {

    }


    public ExcelDictSheetUtil(Workbook workbook,String sheetName) {
        this.dictMap = new HashMap<>();
        this.book = workbook;
        this.dictSheet = workbook.createSheet(sheetName);
        this.dictColIndex = 0;
    }


    /**
     * 新增或添加excel字典
     * @param dictType  字典类型
     * @param excelFieldEntity  excel参数对象
     */
    private   String getOrAddDictToDictSheet(String dictType, ExcelFieldEntity excelFieldEntity){
        String value = dictMap.get(dictType);
        if(StringUtils.isEmpty(value)){
            //设置字典表头
            List<ExcelDictModel> excelDictModelList = excelFieldEntity.getExcelDictModelList();
            List<String> textList = excelDictModelList.stream().map(ExcelDictModel::getText).collect(Collectors.toList());
            if (dictSheet.getRow(0) != null) {
                Cell headCell = dictSheet.getRow(0).createCell(dictColIndex);
                headCell.setCellStyle(excelStyleUtil.getCellStyle(book,ExcelStyleUtil.HEAD_STYLE));
                headCell.setCellValue(excelFieldEntity.getTitle());
            }else {
                Cell headCell = dictSheet.createRow(0).createCell(dictColIndex);
                headCell.setCellStyle(excelStyleUtil.getCellStyle(book,ExcelStyleUtil.HEAD_STYLE));
                headCell.setCellValue(excelFieldEntity.getTitle());
            }
            for (int i = 0; i < textList.size(); i++) {
                Cell dictCell = null;
                if (dictSheet.getRow(i+1) != null) {
                    dictCell = dictSheet.getRow(i+1).createCell(dictColIndex);
                } else {
                    dictCell = dictSheet.createRow(i+1).createCell(dictColIndex);
                }
                dictCell.setCellValue(textList.get(i));
            }
            value = "\"" + dictSheet.getSheetName() + "!$" + CELL_NAME[dictColIndex] + "$2:$" + CELL_NAME[dictColIndex] + "$" + (textList.size()+1) + "\"";
            dictMap.put(dictType,value);
            dictColIndex ++;
            return value;
        }else {
            return value;
        }
    }


    /**
     * 给EXCEL单元格增加校验
     * @param dataSheet 数据sheet页
     * @param excelFieldEntity  excel参数对象
     * @param sort  排序
     * @param startRowIndex 开始行索引
     */
    public void validateData(Sheet dataSheet, ExcelFieldEntity excelFieldEntity, int sort, int startRowIndex) {
        DataValidationHelper dvHelper = dataSheet.getDataValidationHelper();
        if (excelFieldEntity.getExcelDictModelList() != null && !excelFieldEntity.getExcelDictModelList().isEmpty()) {
            // 设置区域边界
            CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, 65500, sort, sort);
            String dictType = "";
            if(StringUtils.isEmpty(excelFieldEntity.getDictType())){
                for (ExcelDictModel excelDictModel : excelFieldEntity.getExcelDictModelList()) {
                    dictType = dictType.concat(excelDictModel.getValue().concat(excelDictModel.getText()));
                }
            }else {
                dictType = excelFieldEntity.getDictType();
            }
            String dropDownStr = getOrAddDictToDictSheet(dictType,excelFieldEntity);
            // 这句话是关键 引用ShtDictionary 的单元格
            DataValidationConstraint constraint = dvHelper.createFormulaListConstraint("INDIRECT(" + dropDownStr + ")");
            DataValidation validation = dvHelper.createValidation(constraint, addressList);
            dataSheet.addValidationData(validation);
        } else {
            CellRangeAddressList addressList = new CellRangeAddressList(startRowIndex, 65500, sort, sort);
            if (excelFieldEntity.getField().getType().equals(Double.class)) {
                DataValidationConstraint constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(Double.MIN_VALUE), String.valueOf(Double.MAX_VALUE));
                DataValidation validation = dvHelper.createValidation(constraint, addressList);
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入双浮点数值！");
                dataSheet.addValidationData(validation);
            } else if (excelFieldEntity.getField().getType().equals(Float.class)) {
                DataValidationConstraint constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(Float.MIN_VALUE), String.valueOf(Float.MAX_VALUE));
                DataValidation validation = dvHelper.createValidation(constraint, addressList);
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入浮点数值！");
                dataSheet.addValidationData(validation);
            }else if (excelFieldEntity.getField().getType().equals(BigDecimal.class)) {
                DataValidationConstraint constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(Double.MIN_VALUE), String.valueOf(Double.MAX_VALUE));
                DataValidation validation = dvHelper.createValidation(constraint, addressList);
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入浮点数值！");
                dataSheet.addValidationData(validation);
            } else if (excelFieldEntity.getField().getType().equals(Integer.class)) {
                DataValidationConstraint constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(Integer.MIN_VALUE), String.valueOf(Integer.MAX_VALUE));
                DataValidation validation = dvHelper.createValidation(constraint, addressList);
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入整数！");
                dataSheet.addValidationData(validation);
            } else if (excelFieldEntity.getField().getType().equals(Long.class)) {
                DataValidationConstraint constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(Long.MIN_VALUE), String.valueOf(Long.MAX_VALUE));
                DataValidation validation = dvHelper.createValidation(constraint, addressList);
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入长整型数！");
                dataSheet.addValidationData(validation);
            } else if (excelFieldEntity.getField().getType().equals(Date.class)) {
                DataValidation validation = null;
                if(dataSheet instanceof XSSFSheet || dataSheet instanceof SXSSFSheet){
                    DataValidationConstraint constraint = dvHelper.createDateConstraint(DVConstraint.OperatorType.BETWEEN, "date(1900,1,1)", "date(2999,12,31)", "yyyy/MM/dd");
                    validation = dvHelper.createValidation(constraint, addressList);
                }else {
                    DataValidationConstraint constraint = dvHelper.createDateConstraint(DVConstraint.OperatorType.BETWEEN, "1900/1/1", "2999/12/31", "yyyy/MM/dd");
                    validation = dvHelper.createValidation(constraint, addressList);
                }
                validation.setShowErrorBox(true);
                validation.createErrorBox("提示", "请输入【1900-01-01,2999-12-31】之间的日期！");
                dataSheet.addValidationData(validation);
            }
        }
    }
}
